--门店配置表：取驿站数据，过滤掉机柜数据

with tmp_station_network as (
    select
        *
    from (
        select
              t1.binding_flag,
              t1.business_id,
              t2.business_name,
              t1.courier_station_code,
              t1.network_code,
              t1.network_name,
              t1.status,
              t2.app_id,
              row_number() over (partition by courier_station_code order by t1.binding_time desc) rn
        from jms_dim.dim_tab_agent_point_base t1            --门店配置表
        left join jms_dim.dim_tab_terminal_secret_base t2   --末端信息配置表
               on t1.business_id=t2.business_id
            where status=1 --启用的
              and binding_flag=1   --绑定的
              and t2.app_id='1010002'   --只取驿站，过滤掉机柜
    ) tmp where rn =1
)

--取出库数据，根据运单号、出库驿站、扫描时间去重取最晚
insert overwrite table jms_dm.tmp_service_quality_bacth_out_store_collect_dt partition(dt)
    select
         waybill_no
       , scan_site
       , scan_site_code
       , post_station_code
       , partner_name
       , storage_address
       , scan_time
       , site_agent_code
       , site_agent_name
       , date(scan_time) as dt
    from (
        select waybill_no
             , t2.network_name as scan_site   --网点
             , t2.network_code as scan_site_code  --网点编码
             , post_station_code  --'驿站/柜编码
             , partner_name  --末端品牌
             , storage_address  --驿站/柜名称
             , scan_time
             , site_agent_code
             , site_agent_name
             , row_number() over (partition by waybill_no,post_station_code order by scan_time desc) rn
        from jms_dwd.dwd_tab_barscan_collect_base_dt t1
        join tmp_station_network t2 on t1.post_station_code=t2.courier_station_code
        where dt between date_add('{{ execution_date | cst_ds }}', -2) and '{{ execution_date | cst_ds }}'
          and date(scan_time) = '{{ execution_date | cst_ds }}'
          and scan_type in ('出库扫描')
    ) where rn = 1
;


--同一驿站、同一时间出库量大于50片的驿站
with tmp_morethanfifty as (
      select  post_station_code
            , scan_time
            , scan_site_code
       from (select post_station_code
                  , scan_time
                  , scan_site_code
                  , row_number() over (partition by post_station_code,scan_time order by scan_time) rn
             from jms_dm.tmp_service_quality_bacth_out_store_collect_dt
             where dt='{{ execution_date | cst_ds }}'
             ) tmp
  where rn = 50
)

--明细
insert overwrite table jms_dm.dm_service_quality_bacth_out_store_detail_dt partition (dt)
select
      date(t1.scan_time) as sum_date
    , t3.scan_time as instore_scan_time
    , t1.scan_time as outstore_scan_time
    , t1.partner_name
    , t1.scan_site_code
    , t1.site_agent_code
    , t1.post_station_code
    , t1.waybill_no
    , t1.scan_site
    , t1.site_agent_name
    , t1.storage_address
    , date(t1.scan_time) as dt
from (select * from jms_dm.tmp_service_quality_bacth_out_store_collect_dt where dt='{{ execution_date | cst_ds }}' ) t1
inner join tmp_morethanfifty t2
        on t1.post_station_code=t2.post_station_code and t1.scan_time=t2.scan_time
left join (
    select
        waybill_no,post_station_code,max(scan_time) as scan_time
    from jms_dwd.dwd_tab_barscan_collect_base_dt where dt between date_add('{{ execution_date | cst_ds }}', -15) and '{{ execution_date | cst_ds }}' and scan_type='入库扫描'
    group by waybill_no, post_station_code
) t3 on t1.waybill_no=t3.waybill_no and t1.post_station_code=t3.post_station_code
distribute by dt,abs(hash(waybill_no)) % 5
;


insert overwrite table jms_dm.dm_service_quality_bacth_out_store_summary_dt partition (dt)
    select t1.dt                         as sum_date
         , max(t1.scan_site_code)        as scan_site_code
         , max(t1.site_agent_code)       as site_agent_code
         , max(t1.partner_name)          as partner_name
         , t1.post_station_code          as post_station_code
         , max(t1.storage_address)       as storage_address
         , max(t1.scan_site)             as scan_site
         , max(t1.site_agent_name)       as site_agent_name
         , count(distinct t1.waybill_no) as cnt_outstore --出库量
         , t1.dt
    from jms_dm.dm_service_quality_bacth_out_store_detail_dt t1
    where t1.dt = '{{ execution_date | cst_ds }}'
    group by t1.dt,t1.post_station_code
distribute by dt
;